import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;


// ** data [ ]
// **  ├ columnName [ ]
// **  ├ resultSet  [ ]
// **  ├ tableName
// **  ├ columnType [ ]
// **  ├ isAuto     [ ]
// **  ├ columnSize [ ]
// **  ├ foreignKey [ ]
// **  └ primaryKey [ ]

public class QueryBrowser extends HttpServlet {

	private static final long serialVersionUID = 1L;

	/**
	 * @uml.property  name="uRL"
	 */
	String URL = "jdbc:mysql://localhost/";
	/**
	 * @uml.property  name="id"
	 */
	String id = "root";
	/**
	 * @uml.property  name="pw"
	 */
	String pw = "massword";
	/**
	 * @uml.property  name="dXL"
	 */
	String DXL; // data xxx language;
	/**
	 * @uml.property  name="query"
	 */
	String query;
	/**
	 * @uml.property  name="schema"
	 */
	String schema;
	/**
	 * @uml.property  name="conn"
	 */
	Connection conn;

	public static String toKSC5601(String str) {
		if (str == null) {
			return null;
		} else {
			try {
				return new String(str.getBytes("8859_1"), "KSC5601");
			} catch (Exception e) {
				return str;
			}
		}
	}

	protected void processRequest(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
		res.setContentType("text/html;charset=euc-kr");
		req.setCharacterEncoding("euc-kr");
		PrintWriter out = res.getWriter();

		query = toKSC5601(req.getParameter("query"));
		String schema = req.getParameter("schema");

		URL = schema != null ? "jdbc:mysql://localhost/" + schema : "jdbc:mysql://localhost/";

		String[] querys;
		querys = query.trim().split(" ");

		Statement stmt;
		ResultSet rs = null;

		DXL = querys[0].toLowerCase();
		String jsonData = "{\"data\": [";

		System.out.println(req.getParameter("count"));

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager.getConnection(URL, id, pw);
			conn.setAutoCommit(false);

			stmt = conn.createStatement();
			ResultSetMetaData rsmd = null;
			DatabaseMetaData dbmd = conn.getMetaData();

			if ("yes".equals(req.getParameter("count").toLowerCase()) && !"insert".equals(DXL)) {
				rs = stmt.executeQuery(query);
				System.out.println("row count: " + getQueryRows(rs) + " record.");
				out.println(getQueryRows(rs));
			} else {
				if ("show".equals(DXL)) {
					rs = stmt.executeQuery(query);
					jsonData = transToJsonData(rs, rsmd, dbmd);
				} else if ("describe".equals(DXL)) {
					rs = stmt.executeQuery(query);
					jsonData = transToJsonData(rs, rsmd, dbmd);
				} else if ("select".equals(DXL)) {// ================== DML
					rs = stmt.executeQuery(query);
					jsonData = transToJsonData(rs, rsmd, dbmd);
				} else if ("insert".equals(DXL)) {
					runQuery(query);
				} else if ("update".equals(DXL)) {
					runQuery(query);
				} else if ("delete".equals(DXL)) {
					runQuery(query);
				} else if ("merge".equals(DXL)) {

				} else if ("create".equals(DXL)) { // ================= DDL
					runQuery(query);
				} else if ("alter".equals(DXL)) {

				} else if ("drop".equals(DXL)) {

				} else if ("truncate".equals(DXL)) {

				} else if ("rename".equals(DXL)) {

				} else if ("grant".equals(DXL)) { // =================== DCL

				} else if ("revoke".equals(DXL)) {

				} else if ("commit".equals(DXL)) {

				} else if ("rollback".equals(DXL)) {

				} else if ("savepoint".equals(DXL)) {

				} else {

				}

				jsonData += "]}";

				System.out.println("URL  : " + URL);
				System.out.println("SQL  : " + query);
				System.out.println(jsonData);

				out.println(jsonData);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void runQuery(String query) throws SQLException {
		Connection con;
		con = DriverManager.getConnection(URL, id, pw);
		PreparedStatement updateSales = null;

		try {
			con.setAutoCommit(false);
			updateSales = con.prepareStatement(query);
			updateSales.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			if (con != null) {
				try {
					System.err.print("Transaction is being rolled back");
					System.out.println(e);
					con.rollback();
				} catch (SQLException excep) {
				}
			}
		} finally {
			updateSales.close();
			con.setAutoCommit(true);
		}
	}

	protected int getQueryRows(ResultSet rs) {
		int size = -1;
		try {
			rs.last();
			size = rs.getRow();
			rs.beforeFirst();
		} catch (SQLException e) {
		}
		return size;
	}

	protected String transToJsonData(ResultSet rs, ResultSetMetaData rsmd, DatabaseMetaData dbmd) {
		String jsonData = "{\"data\": [";

		try {
			rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();

			String columnNames = "{\"columnNames\": [ ";
			for (int i = 1; i < columnCount; i++) {
				columnNames += "\"" + rsmd.getColumnLabel(i) + "\",";
			}
			columnNames += "\"" + rsmd.getColumnLabel(columnCount) + "\"]}";
			jsonData += columnNames;

			/*
			 * String count = "{\"count\":["; rs.getRow(); count += "]}";
			 * jsonData += "," + count;
			 */

			String result = "{\"resultSet\": [ ";
			while (rs.next()) {
				result += "{";
				for (int i = 1; i <= columnCount; i++) {
					result += "\"" + rsmd.getColumnLabel(i) + "\":";
					if ("Rows".equals(rsmd.getColumnLabel(i))) {
						result += ("\"" + rowCount(rs.getString(1)) + "\"");
					} else {
						result += ("\"" + rs.getString(i) + "\"");
					}
					result += (i < columnCount) ? "," : "";
				}
				
				result += "}";
				result += !rs.isLast() ? "," : "";
			}
			result += "]}";
			jsonData += "," + result;

			if ("select".equals(DXL)) {

				// ================================================ table name
				String tableName = "{\"tableName\":\"";
				tableName += rsmd.getTableName(1) + "\"}";
				jsonData += "," + tableName;

				// ================================================ columnsType
				String columnsTypes = "{\"columnsType\": [ ";
				for (int i = 1; i <= columnCount; i++) {
					// java.sql.Types
					columnsTypes += "\"" + rsmd.getColumnType(i) + "\"";
					if (i < columnCount) {
						columnsTypes += ",";
					}
				}
				jsonData += "," + columnsTypes + "]}";

				// =========================================== autoIncrement
				String isAuto = "{\"isAuto\": [ ";
				for (int i = 1; i <= columnCount; i++) {
					if (rsmd.isAutoIncrement(i) == true) {
						isAuto += "\"" + searchMax(rsmd, i, "mysql") + "\"";
					} else {
						isAuto += "\"" + rsmd.isAutoIncrement(i) + "\"";
					}
					isAuto += (i < columnCount) ? "," : "";
				}
				jsonData += "," + isAuto + "]}";

				// ================================================ columnsSize
				String columnsSizes = "{\"columnsSize\": [ ";
				for (int i = 1; i <= columnCount; i++) {
					columnsSizes += "\"" + rsmd.getColumnDisplaySize(i) + "\",";
				}
				columnsSizes += "\"" + rsmd.getColumnDisplaySize(columnCount) + "\"";
				jsonData += "," + columnsSizes + "]}";

				// =========================================== findForeignKey
				String foreignKeys = "{\"foreignKeys\": [ ";

				ResultSet rsFk = dbmd.getImportedKeys(null, null, rsmd.getTableName(1));
				ResultSetMetaData rsmdFk = rsFk.getMetaData();
				int cols = rsmdFk.getColumnCount();
				while (rsFk.next()) {
					foreignKeys += "{";
					for (int i = 1; i <= cols; i++) {
						foreignKeys += "\"" + rsmdFk.getColumnName(i) + "\":\"" + rsFk.getString(i) + "\"";
						foreignKeys += i < cols ? "," : "";
					}
					foreignKeys += "}";
					foreignKeys += !rsFk.isLast() ? "," : "";
					// findPrimaryKey(rs.getString(4),rs.getString(3),rs.getString(8));
				}
				jsonData += "," + foreignKeys + "]}";
				// jsonData += "," + findForeignKey(dbmd, rsmd.getTableName(1));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return jsonData;
	}

	protected String showTable(String table, String jsonData) {

		String columnNames = "{\"columnNames\": [\"COLUMN_NAME\"]}";
		jsonData += columnNames;

		String result = "{\"resultSet\":[";

		try {
			DatabaseMetaData dbmd = conn.getMetaData();
			ResultSet rs = dbmd.getColumns(null, null, table.toUpperCase(), "%");

			ResultSetMetaData meta = rs.getMetaData();
			int n = meta.getColumnCount();

			String[] columns = new String[n];

			for (int i = 1; i <= n; i++) {
				columns[i - 1] = meta.getColumnLabel(i);
			}

			while (rs.next()) {
				result += "{";
				for (int i = 0; i < n; i++) {
					result += "\"" + columns[i] + "\" : \"" + rs.getString(i + 1) + "\"";
					result += (i < n) ? "," : "";
				}

				result += "}";
				result += !rs.isLast() ? "," : "";
			}

			result += "]}";
			jsonData += "," + result;

		} catch (SQLException se) {
		}

		return jsonData;
	}

	protected String findForeignKey(DatabaseMetaData dbmd, String table) {

		String foreignKeys = "{\"foreignKeys\": [ ";
		try {
			ResultSet rs = dbmd.getImportedKeys(null, null, table);
			ResultSetMetaData rsmd = rs.getMetaData();

			int cols = rsmd.getColumnCount();
			while (rs.next()) {
				foreignKeys += "{";
				for (int i = 1; i <= cols; i++) {
					foreignKeys += "\"" + rsmd.getColumnName(i) + "\":";
					foreignKeys += "\"" + rs.getString(i) + "\"";
					foreignKeys += i < cols ? "," : "";
				}
				foreignKeys += "}";
				foreignKeys += !rs.isLast() ? "," : "";
				// findPrimaryKey(rs.getString(4),rs.getString(3),rs.getString(8));
			}
			foreignKeys += "]}";
			rs.close();
		}

		catch (Exception e) {
			e.printStackTrace();
		}
		return foreignKeys;

	}

	protected String findPrimaryKey(String PKCOLUMN_NAME, String PKTABLE_NAME, String FKCOLUMN_NAME) {
		String primaryKeys = "{\"primaryKeys\": [ ";

		try {
			Statement stmt = conn.createStatement();
			String sqlPK = "select " + PKCOLUMN_NAME + " from " + PKTABLE_NAME;
			ResultSet rsPk = stmt.executeQuery(sqlPK);

			primaryKeys += "{";
			primaryKeys += "\"" + FKCOLUMN_NAME + "\": [";
			while (rsPk.next()) {
				primaryKeys += "\"" + rsPk.getString(1) + "\"";
				primaryKeys += !rsPk.isLast() ? "," : "";
			}
			primaryKeys += "]}";
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return primaryKeys;
	}

	protected int rowCount(String table) {

		int count = 0;
		String queryRowCount = "select count( * ) as rowCount from " + table;
		Connection conn;
		Statement stmt;
		ResultSet rsCnt = null;

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager.getConnection(URL, id, pw);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			rsCnt = stmt.executeQuery(queryRowCount);

			if (rsCnt.next()) {
				count = Integer.parseInt(rsCnt.getString("rowCount"));
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return count;
	}

	protected int searchMax(ResultSetMetaData rsmd, int col, String dbms) {

		int max = 0;
		String queryMax = "";

		Connection conn;
		Statement stmt;
		ResultSet rsMax = null;

		try {
			String tableName = rsmd.getTableName(col);
			String columnName = rsmd.getColumnLabel(col);

			if (dbms == "mysql") {
				queryMax = "select ifnull(max(" + columnName + "),'0') as cnt from " + tableName;
			} else if (dbms == "oracle") {
				queryMax = "select nvl(max(" + columnName + "),'0') as cnt from " + tableName;
			} else if (dbms == "mssql") {
				queryMax = "select isnull(max(" + columnName + "),'0') as cnt from " + tableName;
			}

			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager.getConnection(URL, id, pw);
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			rsMax = stmt.executeQuery(queryMax);

			if (rsMax.next()) {
				max = Integer.parseInt(rsMax.getString("cnt"));
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return max;
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,
			IOException {
		processRequest(request, response);
	}
}

// String insertString = "INSERT ";
// insertString += "INTO category (tag, c_name, c2_id) ";
// insertString += "VALUES ('la', 'los angeles', 'la')";
//
// String updateString = "UPDATE category ";
// updateString += "SET tag = 'LA' ";
// updateString +=
// "WHERE tag = 'la' AND  c_name = 'los angeles' AND c2_id = 'la'";
//
// String deleteString = "DELETE ";
// deleteString += "FROM category ";
// deleteString +=
// "WHERE tag = 'la' AND  c_name = 'los angeles' AND c2_id = 'la'";

// String createString;
// CREATE TABLE customer
// (First_Name char(50),
// Last_Name char(50),
// Address char(50),
// City char(50),
// Country char(25),
// Birth_Date date)

// String alterString;
// ALTER TABLE customer ADD Gender char(1)
// ALTER TABLE customer CHANGE Address Addr char(50)
// ALTER TABLE customer MODIFY Addr char(30)
// ALTER TABLE customer DROP Gender